#!/bin/bash
# T日  要计算的数据日期
dt1=$(date -d"-1 days" +%Y-%m-%d)
# 计算数据的前一天
dt2=$(date -d"-2 days" +%Y-%m-%d)

export  JAVA_HOME=/opt/apps/jdk1.8.0_191
export  HADOOP_HOME=/opt/apps/hadoop-3.1.1
export  HIVE_HOME=/opt/apps/hive-3.1.2
export  SPARK_HOME=/opt/apps/spark-3.1.3


echo  $dt1
echo  $dt2
sql="set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrick;
insert into  table dim.cool_device_account_bind partition (dt)
    select
     nvl(x1.deviceid , x2.device_id)  as  device_id,
     nvl(x1.account , x2.account)  as account ,
     case
    when x1.account is not null  and x2.account is not null  then  x1.sess_cnt*10 +x2.weight
    when x1.account is  not  null  and  x2.account is  null  then  x1.sess_cnt*10
    else  x2.weight * 0.7  end  as  weight ,
    if(x1.account is not  null , '${dt1}' , x2.last_login) as  last_login ,
    '${dt1}' as dt
    from

    (
            select
            deviceid ,
            account ,
            count(distinct  new_session)  as sess_cnt
            from
            tmp.cool_app_with_region where dt = '${dt1}'
            and account is not null and trim(account)!=''
            group by deviceid , account
    )x1
    full join

    (       select
            device_id ,
                   account ,
                   weight ,
                   last_login
            from
            dim.cool_device_account_bind where dt ='${dt2}'
    ) x2
    on x1.deviceid||x1.account = x2.device_id||x2.account"
hive -e "${sql}"

